Kevin S. Goff: Life in Detail w/Microsoft Database Tools

3/27/2012 – Video recordings from yesterday’s SQL Server 2012 Webinar For SetFocus

Here are the videos from our SetFocus webinar on new features in SQL Server 2012:

Day 1 (3/27/2012) covered the new Columnstore Index, new T-SQL Features, and new SSIS Features
Day 2 (3/28/2012) covered the new features in Analysis Services tabular model, PowerPivot 2.0, and Power View

Day	Early morning	Mid-morning	Early afternoon	Late afternoon
3/27/2012	Video 1 of 4	Video 2 of 4	Video 3 of 4	Video 4 of 4
3/28/2012	Video 1 of 4	Video 2 of 4	Video 3 of 4	Video 4 of 4

Each video is anywhere from 60 to 90 minutes long. Each download is a zip file with a WMV file inside that can be played with Windows Media Player.

Again, we invite everyone to check out our SetFocus Free Training portal, with over 100 hours of free video training on .NET, SQL Server/BI, SharePoint, and other topics.

Feel free to email me at kgoff@setfocus.com if you have any questions. 

10/10/2011 – Free Training from SetFocus

Whether you're a seasoned veteran or a rookie, everyone in this industry eventually finds themselves back at square one, trying to wrap their minds around a particular technology. You could be a mainframe or an AS/400 programmer who now needs to learn SQL Server….or you could be a VB programmer who needs to ascend the object-oriented programming learning curve….or you could find yourself in a situation where you need to learn the basics of SQL Server Integration Services very quickly.

Maybe you need to learn this content as a step towards getting a job, or just looking to strengthen your skills.

Yes, there's a large amount of content on the web – often scattered all over the place. Once in a great while you might find a single website or article that covers exactly what you need. Other times (quoting a famous musician) it can feel like "wading through a cesspool in search of a water lily".

Well, the company where I work as an instructor and a practice manager (www.setfocus.com) has done something rather amazing about this.

We've made some of our training content available on the web, FOR FREE.

Our CEO (Mike Landau) and our president (Jerry Masin) talk about this free training initiative in an article in NJBIZ: Mike Landau also talks about this during an interview. (Note: on this interview link, you'll need to right-click and open in a new browser window).

If you're interested, here's how to get started:

First, the site is http://freetraining.setfocus.com. The free registration process is simple. You create an account (near the upper right corner), provide a valid email address and some general information (name, city, reason for joining). You will NOT be prompted to enter a credit card. (Many websites claim to offer free content, but still ask for a credit card). Free means free.

You'll receive a confirmation email, and then you can enroll in any of our free training courses. Here's a list of what we offer for free:

• An online course on Object Oriented Programming with C#. There are slides, videos, and labs on OOP basics, Class design, inheritance, interfaces, abstract classes, and more. Some of the content comes from our .NET Master's Program.
  
  
• An online course on Relational Database Management concepts. There are slides and videos on relational database models, data modeling, normalization, data integrity, and DBA concepts. Some of the content comes from our SQL Server Master's Program.
  
  
• Video content from our Business Intelligence Master's Program. There are videos on SQL Server Integration Services, MDX Programming, plus a link to some online articles and blog entries on different BI topics. This content comes from our Business Intelligence Master's Program.
  
  
• Video content from our SharePoint Master's Program. These videos cover SharePoint 2010 Forms-based authentication, creating Silverlight applications in SharePoint, creating workflows, creating custom ASP.NET pages, and creating input forms using InfoPath. This content comes from our SharePoint Master's Program.
  
  
• Video content on how to get the most out of LinkedIn. You might be thinking, "videos on how to use LinkedIn?" That's right – LinkedIn has a number of videos on getting the most out of LinkedIn, and getting your name "out there". At first, I was a bit skeptical about LinkedIn – the old "quantity vs quality argument". But I've come to see the value. With a job market that gets more competitive every day, business-related social networking sites can be a valuable tool. You never know when a connection might lead to something!

In addition, back in July I did a 3+ hour webinar for SetFocus on the entire Microsoft Business Intelligence platform. The webinar contains segments from our Business Intelligence Master's Program. (The same program mentioned in point 3 above). The link for this 3+ hour webinar is http://www.setfocus.com/onlinecourses/bi/bi.aspx.

While we have a substantial amount of free content, we'll continue to evolve this free training portal over time.

Finally, because I believe in telling people the whole truth, SetFocus might contact you via email to see if you have any further training or career services needs. But there's no obligation, no strings attached. Having said that, I'd be remiss if I didn't also mention the unique strengths and competitive advantages that SetFocus offers (both in training and career services), which I blogged about a while back.

SetFocus is a Microsoft Gold Partner for Learning Solutions with Microsoft technologies. They have a long history of direct involvement in software community efforts. For years, they've hosted meetings for the Northern New Jersey .NET User Group, the New Jersey SQL Server User Group, and the New Jersey SharePoint User Group at their facility in Parsippany, NJ. They're also supportive of my community efforts as a speaker at different CodeCamp, SQL Server, and SharePoint events (not all companies are supportive of community efforts). This free training initiative is further proof.

So, if you're interested, take a few minutes and sign up. You have nothing to lose, and possibly things to gain!

11-07-2010 - SetFocus BI/SQL Master's Program Tip: SSRS 2008/SSRS 2008 R2 OLAP reports and overwriting MDX parameters

In the SetFocus Business Intelligence Master's Program, I show many examples of building SSRS reports against Analysis Services (OLAP) databases. I've actually been showing this going back to SQL Server 2005.

SSRS 2008 (and SSRS 2008R2) have some tremendous reporting enhancements, but unfortunately introduced a feature that caught some people by surprise. The issue is a rather intricate one, and goes as follows:

Suppose you create a report against an OLAP cube. You start by creating a report dataset, using the OLAP query designer. You drag some columns from the cube metadata into the result set area, and you also define one of the OLAP dimensions as a parameter. SSRS creates a second dataset with MDX code that retrieves member values from the dimension attribute you defined as a parameter in the primary dataset. (This allows SSRS to display the parameter values in a report dropdown).

With me so far?

Now suppose you want to modify the MDX code for the 2^nd dataset (which SSRS builds as a hidden dataset), to implement some custom behavior for the dropdown. For example, instead of showing all cities in a dropdown, you only want to show those cities with sales (or sales in the last 6 months, etc.) So you retrieve the MDX code for the 2^nd (hidden) dataset, and you modify the MDX code. You run the report again, and the dropdown shows values the way you want to show them.

OK, so life is good…but this story isn't over.

Now go back to the original (primary) dataset and add a column/measure. (For example, maybe initially the report had 4 measures, and you have to add a fifth). So you add a measure to the primary dataset, you save it, you modify the report layout to add the new measure/column, and then you run the report…

And then you discover something – the dropdown parameter isn't showing the values in the dropdown according to the logic you implemented when you manually modified the MDX code. As a matter of fact, the dropdown is showing the available parameter values as if you'd never modified the MDX code at all!!!

Here's what happened – when we changed the primary dataset to add a new column, SSRS (well, specifically, Business Intelligence Development Studio) overwrote our custom MDX code with the original MDX code for the dimension parameter!!!

OUCH! This is actual behavior in SSRS 2008 and 2008 R2. As for a workaround, there's good news and bad news. The good news is that there's a workaround in the form of a setting. The bad news (well, more like "not great news") is that BIDS doesn't expose the setting – we need to modify the actual XML code in the RDL file.

So let's look at an actual example:

Suppose we create a simple OLAP report against AdventureWorks 2008. We want to show Internet Sales Amount by Product Category, and we want the user to select a country from the Customer Dimension/Country Attribute (Figure 1)

Figure 1:

   

After we save this dataset, SSRS creates a 2^nd dataset that retrieves the countries from the Customer dimension. By default this 2^nd dataset doesn't show in the DataSet window pane. However, we can right-click and show Hidden Datasets, which will wind up displaying the 2^nd dataset (which SSRS called CustomerCountry, in Figure 2):

   

Figure 2:

   

If we right-click on this 2^nd dataset and modify the query, SSRS loads the query designer with the generated MDX code (Figure 3). The code retrieves all countries in alphabetic order, plus the All Country (Customer) total.

   

Figure 3:

   

Now suppose we want to modify the query (and therefore the contents of the dropdown) to show the countries in order of sales for the country. So we modify the MDX query to use the MDX ORDER function, to show the countries in order based on sales, in Figure 4: (i.e. country with the highest sales will be first in the dropdown)

Figure 4:

   

Now we run the report (which shows Product Category and Internet Sales amount, and the prompt for Country with the correct sort order), in Figure 5:

   

Figure 5:

   

OK, now let's go back to the original main dataset, and add a new column (Internet Gross Profit), in Figure 6:

Figure 6:

We add the column to the designer as well, and then go run the report (Figure 7). Notice how the dropdown appears to have "lost" the behavior we implemented with our custom MDX code. This is the root of the behavior in SSRS 2008. What happened was that when we modified the dataset in the previous step, SSRS overwrote our custom MDX changes (without warning us!) To prevent this behavior, we need to modify the RDL file manually, to add a new setting (next step).

   

Figure 7:

   

We need to open the RDL file manually, which you can do by right-clicking on the report in Solution Explorer and taking the option "View Code". At the end of the XML content for the hidden dataset, add the following line: <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> (Figure 8).

Figure 8:

Now to test, go back and repeat step 4 (to put the custom MDX code back into the hidden dataset), and then pull up the main dataset and add another column, such as Internet Freight Cost (Figure 9). Previously, this would overwrite our custom MDX code. However, because of the SuppressAutoUpdate setting, SSRS will not update the MDX code.

Figure 9:

   

So if we go run the report after making the dataset change, our MDX code now stays!

Figure 10:

05-24-2010 - SetFocus BI/SQL Master's Program Tip: Change Data Capture in SQL Server 2008

One of the important new features in SQL Server 2008 is Change Data Capture (also known as CDC). CDC implements change tracking into the database management system. (Change Data Capture, as a concept, has been around for many years. You can search on the term and find a good history of information on it.)

Before I dive into CDC in SQL Server, a little history: until SQL Server 2008, if we wanted to implement audit trail history (i.e. log insertions, updates, changes), the most effective method was through database triggers. Even with the new OUTPUT/OUTPUT INTO feature in SQL Server 2005, triggers were still the most reliable and effective way to detect and log the "before/after" values the audit logs require. And with any form of audit trail processing, reliability is ABSOLUTELY CRITICAL. Many applications have strict requirements for audit trail processing, to view historical changes (along with who made the changes, and when).

While database triggers are great (I've used them for years and I love them), critics make a valid point that the database management system should provide some sort of audit log "out of the box". And so Microsoft implemented Change Data Capture in SQL Server 2008. In both the SetFocus SQL Server Master's Program and Business Intelligence Master's Program, I cover Change Data Capture in SQL Server 2008. Here's an overview and a brief example of CDC.

First, I need to make sure SQL Server Agent is running. CDC in SQL Server 2008 relies on SQL Server Agent jobs.

Second, I'll create a new test database called CDCTest.

Third, I'll run a new system stored procedure that enables CDC on a database. I only need to run this one time.

Fourth, I'll create a very simple database table called TestEmployees. (Eventually I'll insert/update/delete rows, and will expect SQL Server to log all the changes).

Fifth, I need to tell SQL Server to log any insertions/updates/deletes for this table. So I'll call another new system stored procedure, to activate CDC for the table:

Note that SQL Server will create a new system table called cdc.dbo_TestEmployees_CT. This is the change tracking table for TestEmployees. (So as you can see, CDC maintains a separate log table for each "regular" database table).

Sixth, I'll insert and update and delete a few rows (to give the log file something to work with):

OK, step 7: let's look at the change tracking log for the TestEmployees table (the name of the change tracking log is derived from the base table name):

The CDC log takes some time to get accustomed to: the _$operation column isn't intuitive (2 is an Insert, 3 and 4 are Old Value/New Value combinations for updates, and a 1 is a deletion), and we need to join the Log Sequence Number column to the system table cdc.lsn_time_mapping in order to retrieve the date and time and transaction number of each operation.

Now for the $64,000 question – suppose I add a column to the original TestEmployees table – will the underlying CDC processes automatically adjust and log any new columns?

There's bad news and there's good news. The bad news is that CDC will not automatically adjust the changing tracking log table, if I add a column to the original table. (This is rather unfortunate, as new columns are a fact of life). The good news is that we can write a little bit of database code to get around this. This involves 4 steps:

First, I need to backup the change tracking log into a temporary database. (The reason is that step 2 will erase the current change tracking log).

Second, I'll add a column to the TestEmployees table (a column for each employee's department):

Third, I'll disable (and then re-enable) change tracking for the TestEmployees table. (This will delete the change tracking table and then recreate it with the new structure that utilizes the new Department column:

Fourth, I'll insert the temporary log back into the newly created log (but before that, I'll add the same new department to the temporary log, to make the insert back into the new log clean). And then after that, I'll drop the temporary log.

Finally, I'll update the department on one of the existing rows, I'll insert a new row, and then I'll read the change tracking table again (which now contains a reference to the new department column).

I hope you enjoyed this walkthrough on CDC in SQL Server 2008. Again, in both the SetFocus SQL Server Master's Program and Business Intelligence Master's Program, I cover CDC in greater detail.

03-08-2010 - SetFocus BI/SQL Master's Program Tip: Sending an email inside of SSIS to a Gmail account

A student in one of my recent SSIS classes asked if it's possible to send an email to a Gmail account, from inside SQL Server Integration Services.

The short answer is "yes", you can.  Of course, the "how" is always in the details - and there are a few details.

First, we can't use the built-in SSIS email control flow task.  Among other reasons, we need to specify a port number and we need to handle a few other properties for credentials that the send mail task doesn't expose. 

So the answer is to write a script task that does the following:

• Creates an SMTP Client object (and objects for a Mail Message and an Attachment as well)
• Enables SSL
• Sets the SMTP client object credentials to the email credentials for the sender

This example uses a Gmail account for both the sender and recipient.  If the sender were using something other than Gmail, then adjustments would need to be made to account for the sender's email.

Here's the code for the SSIS task, in C#.  (Remember that the very top of the SSIS script would need to add references to the System.Net and System.Net.Mail namespaces).

 

1-17-2010 - SetFocus BI/SQL Master's Program Tip (from student alumnus question): when PREVMEMBER alone isn't enough in MDX

A rather common requirement in OLAP reporting is to show sales for a specific day, and for the previous day. Using AdventureWorks as an example, if we wanted to see sales by city for a specific date (e.g. March 1, 2004) and then show sales for the previous day (as a generic calculation), we can use the MDX PREVMEMBER statement and write the following MDX code:

WITH MEMBER [SalesPriorDay] as
    ([Internet Sales Amount],
     [Date].[Date].CurrentMember.PrevMember) , format_string = 'currency'

select { [Internet Sales Amount], [SalesPriorDay]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

That code will produce the following result set (partial screenshot):

Recently, a student who just graduated from the SetFocus BI Master's Program emailed me and essentially asked me the following: suppose we want the column for the previous sales day to be more intelligent...i.e., suppose that for one dimension member, the 3 days prior had no sales at all, but going back 4 days, sales existed. And for another dimension member, maybe you had to go back two days to get sales, etc.

Essentially, the recent graduate was asking for a "smarter" SalesPriorDay calculation - one that would go back and find the last day where sales existed. He also asked if the result set could show the day.

To write something as generic as possible, we have to first acknowledge that the last prior day where sales existed could theoretically go back to the very first day of the database. So in plain English, we need to do the following:

• Retrieve all the days prior to the "base" date that had sales for the current member (in this case, city).
  • To do this, we need to create a range from the first possible date of sales, up to the day prior to the "base" date    
• From that list of days with sales, grab the last (most recent) date

Well, let's break that up into small pieces: first, to create a range of dates from the first possible date to the date prior to the "base" date, we can use the MDX Range operator (:)

{ [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}

Next, we can use the MDX filter function against that range of dates, to build a smaller set of dates where sales occured for the current member (remember that MDX is smart enough to pick up the context of the current date and each city in the row axis).

filter( {[Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0)

OK, we're close. We'd like to take the last date from the result of this filter - that would represent the last actual date where sales occured, for whatever dimension members we're using in the main query. We can use the MDX TAIL function to grab the last item in the filtered set:

tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1)

So the hard part is over, right? Not exactly. Our goal is to express the measure (Internet Sales Amount) in terms of a dimension member that represents the previous date where we have sales. But doesn't the last line fo code provide the dimension member representing the last actual date? No....remember that TAIL returns a SET, even if we provide a value of 1 for the 2nd parameter. So we have to use the ITEM() function to grab the first member of the result from the TAIL function - even if the results from TAIL only contain one tuple expression:

tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1).Item(0)

Now we can create a "smarter" calculation for Sales Prior Business Day, where that day could be several days or even weeks prior. Here's the entire solution. Note that we also show the actual date as a separate member, using the Name property.

with MEMBER [SalesPriorBusinessDay] as
    ([Internet Sales Amount],
    tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'

MEMBER [Measures].[LastSaleDate] AS
    tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0).name

select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

The results (below) show the sales for the date of March 1, 2004 - as well as the last actual date of sales. So notice that Ballard last had activity on February 27th, and that you have to go back an additional week to show sales for Bendigo

There is one issue with this approach - the extra logic could provide very costly, especially if we're repeatedly filtering on a large number of days. An alternate approach might be this: instead of creating a range that goes back to the first possible day of sales, let's only go back 1 week. If we don't have sales for any point in the prior 7 days, then we'll simply settle for a NULL value. In this case, we can use the MDX LAG function instead:

WITH MEMBER [SalesPriorBusinessDay] as
    ([Internet Sales Amount],
    tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'

MEMBER [Measures].[LastSaleDate] AS
    tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0).name

select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

Using LAG will result in the following output:

So in the case of Bendigo, we consider any sales prior to the previous 7 days to be "very old". So the need for specific reporting may be need to be weighed against the consequences of slower performance.

Bottom line, the student was able to take this approach and run with it. I always feel happy when I'm able to give a graduate a hand on a query that he/she is working on, either for a job or just as part of continuing education. I certainly won't say that I'm able to answer every question from every former student - some questions are just too intricate and require that I have the data in front of me. Other cases I might be able to make some suggestions, and/or give them some websites where they might be able to get more information. But on this particular day, I got lucky. :)

At SetFocus, we often add content to our courseware, based on feedback and also based on questions/interactions. So in this instance, our MDX class will now include an example based on what I've covered here.

12-23-2009 - SetFocus BI/SQL Master's Program Tip: Resolving the issue of multiple JOIN statements and multiple aggregations

We've all made big mistakes in our career - and so we all know that one thing worse than making a big mistake, is making a big mistake that goes undetected for a long time!

The following is an excerpt from a lesson plan in our SetFocus SQL Server and Business Intelligence Master's Programs.

Consider the following: suppose we have 3 tables in a SQL Server database: JobMaster, JobHours, and JobMaterials. They represent a simple example of multiple one-to-many relationships. A single job in JobMaster can have multiple timesheet rows (or none at all) in JobHours, and multiple material purchase rows (or none at all) in JobMaterials.

So our tables might look something like this:

 

So there are four possibilities:

• A job can have both hours and material purchases (Job A)
• A job can have hours but no material purchases (Job B)
• A job can have material purchases but no hours (Job C)
• A job (likely a new job) may not have data in either child table (Job D)

Now suppose we need to write a query that retrieves each Job in JobMaster, along with the sum of Hours Worked (from JobHours) and the sum of Material Purchases (from JobMaterials). The result set should show all jobs - even those with no child data, and would look something like this:

JobDescription        TotalHoursWorked    TotalPurchaseAmount

 

Many SQL developers, recognizing that a LEFT JOIN is required to show all jobs, might write the code as follows (below). The code performs a LEFT JOIN between the parent table and two child tables, and aggregates (SUMs) the HoursWorked and PurchaseAmount from the two tables. This produces the result set in the result set window (below).

 

At first glance, the output above appears correct.

• Job B did not have material purchase amounts, so we see a NULL for the aggregation, and the correct number of 26 hours (7 + 7 + 6 + 6) for the aggregation of hours worked.
• Job C did not have hours worked, so we see a NULL for the aggregation, and the correct number of $30,000 ($10,000 + $20,000) for the aggregation of material purchases.
• Job D is a new job and doesn't have any child data, so we see NULLs for both aggregations.

At this point, some might be tempted to "bless" the code and results. Surely, the one Job we didn't test (with both hours and purchase amount) is the simplest scenario, and "has" to be correct, right? :)

Well, you probably see where this is going! Take a closer look at the raw data for Job A.

• The output shows 60 hours, though the raw data contains 30 hours ( 8 + 8 + 7 + 7)
• The output shows $12,000 in purchase amount, though the raw data contains $3,000 worth ($1,000 + $2,000). Talk about double (and quadruple) counting!

Further, we notice something else - there were 4 Job Hour rows for Job A, and the material purchase aggregation was off by a factor of 4.
Additionally, there were 2 Material Purchase rows for Job A, and the Job hour aggregation was off by a factor of 2.

COINCIDENCE? No, it's not a coincidence. As it turns out, when we use multiple JOIN statements (either INNER or OUTER joins), and try to perform multiple aggregations on multiple one-to-many relationships, you risk overstating the aggregations. In this case, the 2 instances of a JOIN between JobMaster and JobMaterials (for Job A) wind up joining 2 instances of Job A to the Job Hours table - thus the double-counting of hours. Likewise, the 4 instances of a JOIN between JobMaster and JobHours (for Job A) wind up joining 4 instances of Job A to the Job Materials table - thus overstating the aggregation of material purchases by a factor of 4.

I know developers who have written the same general code as the code above - and didn't realize until much later (usually, when a user or customer discovered high number) that this was a problem waiting to happen.

OK, so we've uncovered the problem - what's the solution?

Before we look at a solution, we need to realize one thing - just like a single long code listing, it's often difficult (or impossible) to solve a problem in one self-contained SQL statement. In this specific situation, we need to "pre-aggregate" JobHours and JobMaterials independently of each other, in some type of subquery. Then we can JOIN the pre-aggregations to the JobMaster as a 1-1 relationship.

In the code below, we can use the Common Table Expression capability in SQL Server 2005 to create 2 CTEs that pre-aggregate Hours and Materials. Then in the next line of code, we perform LEFT JOINs between JobMaster and the 2 CTEs, and that produces the correct results.

 

 

Of course, there's often more than 1 way to write a query. Some might prefer to write a 2 correlated subqueries, each of which retrieves a single aggregated amount for every job in the outer query (see below). Of course, there are always debates about whether a correlated query might run a bit slower and might take up additional resources. We cover these issues in the Master's Program classes.

 

If there's a moral of this story, it's that sometimes our safest assumption might be the very root of the problem!

12-21-2009 - SetFocus BI Master's Program Tip: Reason #1 (of a list of thousands of reasons) why MDX is important

During our PerformancePoint Server class in the SetFocus Business Intelligence Master's Program, we cover analytic charts and analytic grids - two powerful ways to incorporate image-style output (i.e., output that can fit on a single screen) into a PerformancePoint Server dashboard. We also cover the role of MDX in coding the necessary calculations to support the charts/grids.

One example is the screen shot below, where we create a dashboard page that allows the user to select any product in the Product Hierarchy (either a high level category or a detail level SKU). The page then generates a chart that shows the following:

• A column chart that plots revenue (Dollar Sales) over the last 12 months of available data
• A line chart (on a secondary Y-axis) that shows each product's sales as a % of the product's parent (in the product hierarchy)
• A line chart (also on a secondary Y-axis) that also shows the calculation of product's sales as a % of the product's parent, but for the same time period a year ago

It should be noted that PPS analytic charts are smart enough to plot the percentages on a dual Y-axis, since a % is on a different scale than the revenue measure.

The dashboard page also generates a data grid recap that shows all of the siblings of the selected product (i.e. all of the products that share the same hierarchical parent of the selected product), using the MDX SIBLINGS statement, and the % of parent Sales for by month. The data grid recap also navigates up the product hierarchy from the user selection, to show the same % of Parent calculation for each parent level. So in this example, we're showing the following:

• GamePlace 2 and all its siblings
• Video Games (the parent of GamePlace 2)
• Electronics (the parent of Video Games)

The chart helps to spot upward or downward trends in a product's share, and also to verify any trends based on seasonality over the two year period. The data recap at the bottom provides additional information on other products that share the same parent. A category manager might use this to (among other things) spot product cannibalization, where product sales decrease due to sales of a newer product.

In the chart below, we see that sales for GamePlace 2 took two significant nose-dives in 2005: in July of 2005 and again in September/October 2005. We can check the analytic grid at the bottom to see which products had a % of Parent increase during that same time period.

OK, so where does the MDX come in? Well, first we need to create calculations to support [Pct of Parent] and [Pct of Parent LY]. We can use Analysis Services to place the following MDX calculations into the cube area:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent] AS

     [Measures].[Dollar Sales] /

         ( [Measures].[Dollar Sales],

         [Product].[ByCategory].CurrentMember.Parent)

   

CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent LY] AS

    ( [Pct of Parent],

        PARALLELPERIOD([Time].[YQMD].[Year], 1))

   

What's great about the first calculation is that it will for any level in the product hierarchy. If I run the chart for a product SKU, the MDX code will use the $ Sales for the SKU's parent in the denominator: likewise, if I run the chart for a product subcategory, the MDX code will use the $ sales for the product subcategory's parent in the denominator. So we can re-use this calculation for any product category level.

What's so great about the 2nd calculation is that we can use the MDX function ParallelPeriod to go back one year, and express the first calculation in terms of the "same time period a year ago". So if we're looking at a measure for Feb 2005, this code will give us the measure in terms of Feb 2004. If we're looking at a measure for Q2, 2005, this code will give us the measure in terms of Q2, 2004.

Note how neither calculation receives the time period (in this case, Months along the bottom axis) as a parameter - this is because MDX calculated members do not receive parameters. Instead, they pick up the context in which they're being run. In other words, they are intelligent enough to pick up that they are being run in the chart (i.e. "sliced") by month.

OK, so that covers the calculations for the chart - how about the analytic grid at the bottom? How are we able to show the selected product and its siblings, along with each parent product, all the way up to the top level? Well, we can do the following:

• Create one MDX set of the siblings of the User Selection <<ProductSelection>>.Siblings
• Create a 2nd MDX set that traverses the hierarchy upwards to retrieve all the parents of the User Selection: ascendants( <<ProductSelection>>)
• Use the MDX UNION function to combine these two sets together
• Use the MDX HIERARCHIZE function to re-order the resulting UNION set in the original Product hierarchical order (from top to bottom)

   

SELECT [MostRecent12Months] ON COLUMNS,

    hierarchize(

        union( ascendants( <<ProductSelection>>),

                    <<ProductSelection>>.Siblings ) ) ON ROWS

FROM [Sales]

WHERE [PctOfParent]

   

This is but one of many examples we cover in the MDX and PerformancePoint Server weeks of the SetFocus Business Intelligence Master's Program.

12/4/2009 - SetFocus BI Master's Program Tip: Testing SSAS Roles in Excel

In the SetFocus Business Intelligence Master's Program, we cover the use of security roles in SSAS databases.

SQL Server Analysis Services 2005/2008 both allow you to define roles for when your Windows users (or groups of users) access OLAP data.

As an example, suppose in the AdventureWorks OLAP database, we wanted to associate users with a role that only allows visibility to data for the countries of Australia and the United Kingdom, for the product category of Bikes. You would create a new role in BIDS (or in SSMS), call the role something like TestBikeRoleForAustraliaAndUK, and make the following selections in the Dimension Data area for the Customer and Product Dimensions (next 2 screen shots):

   

   

You can test the role by going to the cube browser in BIDS - the cube browser provides the ability to view the contents of the cube as if you were a user associated with the new role of TestBikeRoleForAustraliaAndUK. But suppose you wanted to test the role in Excel? After all, many users prefer to use Excel to create Pivot Tables and Pivot Charts from OLAP data. But how can we open the cube in Excel and test the role, without needing to actually sign on as a user associated with that role?

It's actually quite easy. In Excel, you can create a connection to an Analysis Services database. You can open the connection properties in Excel, and set the Roles property in the connection string to the role you wish to test (see screen shot below).

   

After making the connection, you can access the PivotTable field list in Excel, and select the entire Customer Geography Hierarchy and Product Category Hierarchy (see screen shot below). However, since you're accessing the OLAP cube based on the Role in the connection string, you'll only see data for Bikes for Australia and the U.K. (screen show below that).

   

   

So as you can see, Excel provides us with the ability to test a role by placing the role in the Connection String property. Pretty slick!

11/8/2009 - SetFocus successfully places 39 candidates in one month!!!

Most people who know me, know that I work as a full-time contract trainer and courseware author for SetFocus, LLC.
SetFocus is a Microsoft Gold Partner that provides learning solutions and career placement services in the Microsoft technology space.

I've been working with them since early 2007, focusing in their Business Intelligence and SQL Server curriculum areas.

SetFocus offers career placement services, which is the subject of this post. Over the last several months, even amidst all the economic problems and challenges, the SetFocus career placement people have been doing an incredible job of placing students in different I.T. positions. These positions have been for software/Business Intelligence Developers, Data Analysts, DBAs, etc.

Well, just a few days ago, I learned that SetFocus placed 39 candidates in October 2009. You can read the press release here.

I know a number of the 39 candidates, because they were students of mine. So believe me, this isn't hype, not one little bit (and those who know me well, know that I'm not one to promote sales hype) - this is the result of some truly remarkable effort and dedication.

I play a role, one of many roles in the SetFocus process, and I'm very proud to do so. We're all raised to believe that business "is business", and therefore shouldn't be personal. However, the people who promote this concept have overlooked something: business isn't conducted by robots and machines, but by human beings. And what could be greater than giving other people a hand to help them get jobs (or better jobs) to support themselves and their families?

From 2004 to 2008, I penned The Baker's Dozen series in CoDe magazine. Job demands and new family demands (now that I'm a daddy, haha) have kept me from writing, but I still think in terms of 13. :)

And so, in celebration of SetFocus pulling off this impressive achievement, here is a special Baker's Dozen: 13 reasons (in no order of importance) why SetFocus could be right for you

1. They utilize video-conferencing for their training. It's normal to have a class of 12-15 people from Seattle to Vancouver to Phoenix to Atlanta. So just because you live three time zones away doesn't mean you have to travel to their corporate location in New Jersey to attend training - they'll come to you. At least 80% of our students participate via our GRID online training system.
   
   When I started with them, I admit I was skeptical about this - but I realized very quickly that their infrastructure for remote training is amazing. After the first day, you realize just how close it is to a "single physical classroom". Click here to learn more about their online training.
2. Their career services/job placement people bust their butts. You can't pull off those kinds of placement numbers without some serious hard work and skill.
3. The general curriculum has a substantial amount of hands-on. There are full project weeks to apply what you've learned.
4. SetFocus has a growing network of business partners, and has been on the Inc. 5000 list.
5. They've been around since 1997. Now, to some people, sheer longevity numbers don't mean very much. At one time I might have agreed - but the last 8 years have taught us that the economic survivors have been far more than just lucky or fortunate.
6. I've worked as a developer/consultant in this industry since 1987. Many times I've met people who graduated from technical schools that claimed they taught "XYZ". Well, I worked with them long enough to be rather suspicious about technical schools in general. All my life, even when I was a contractor and an author, I wanted to be a teacher - and swore that if I ever had a position of influence in a technical school, I'd do things very differently. Well, here I am, as a full-time trainer and curriculum author, and every day for the last 3 years that's been my goal - to arm students to the teeth with information about software tools and how to use them in different business application settings.
7. The trainers and technical support staff are incredibly dedicated - these people are almost constantly putting in time in the evenings and weekends to help students.
8. We don't stand still. We often receive feedback from alumni on ways to improve the program, on content we should add in the future.
9. We prepare people for technical interviews. Several of our instructors have previously been on both sides of the interview table.
10. SetFocus is a Microsoft Certified Gold Partner for Learning Solutions.
11. We will challenge you - we will throw a large amount of content on your plate - we will push you - sometimes you will love us for it, and some days you won't love us for it. Yes, that is the price of success. But as much as we possibly can, we are here to help. (Remember that old line - if something were easy, everyone would be doing it!)
12. The sales reps/placement people will do everything they can to help people get enrolled in the classes that are right for them. On a regular basis, reps will ask people from the training team to talk to potential candidates, either to answer questions, address concerns, etc. The reps will stay in touch with you throughout the training, whether you take a 1 week class or a 2 week class or the full-blown 10 week Master's program.
13. The classes are NOT cookie-cutter style classes. You will get perspectives from people who have used these tools in production.

Now, there are more than 13 reasons why SetFocus could be right for you. But if you are looking for training in different Microsoft technologies, hopefully this will help you in your decision-making.

So again, SetFocus (and to the 39 students), congratulations on this huge achievement. Two thumbs up!

KG